package DBLayer;

/**
 * DBCustomer.java
 * @author Brian Lassen
 * @version 20. Marts 2012
 */
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBCustomer implements IFDBCustomer{

    private  Connection con;
    
    public DBCustomer() 
    {
      con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList<Customer> getAllCustomer(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    public Customer findCustomer(int cusid, boolean retriveAssociation)
    {   
    	String wClause = "  id = " + cusid + "";
        return singleWhere(wClause, retriveAssociation);
    }

    public Customer searchCustomerId(int attValue, boolean retriveAssociation)
    {
        String wClause = "id = " + attValue + "";
        System.out.println("Search Customer: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public Customer searchCustomerName(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("Search Customer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    public Customer searchCustomerAddress(String attValue, boolean retriveAssociation)
    {
        String wClause = "address = '" + attValue + "'";
        System.out.println("Search Customer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    public Customer searchCustomerphone(int attValue, boolean retriveAssociation)
    {
        String wClause = "phone = " + attValue + "";
        System.out.println("Search Customer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    
    public String searchCity(int zipcode)
    {
    	String query="SELECT city FROM ZipCity WHERE zipcode = " + zipcode + "";
    	ResultSet results;
    	String city = "";
    	
    	try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			city = ""+results.getString(1)+"";
	 			stmt.close();
			}
		
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
	 	
	 	return city;
    }

    public int insertCustomer(Customer cust)
    {
    	int nextId = GetMax.getMaxId("Select max(id) from Customer");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);
    	
       int rc = -1;
	   String query="INSERT INTO Customer(id, fname, lname, address, zipcode, phone)  VALUES(" +
	   		nextId  + ",'"  +
		    cust.getFname()  + "','" +
		    cust.getLname()  + "','" +
            cust.getAddress() + "'," +
            cust.getZipcode() + "," +
            cust.getPhone() + ")";
                     
      System.out.println("insert : " + query);		  					
      try{
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in customer db: "+ex);
	   }
	   return(rc);
	}
	
	public int updateCustomer(Customer cust)
	{
		Customer custObj  = cust;
		int rc=-1;
	  
		String query="UPDATE Customer SET " +
		 	 "fname ='" + custObj.getFname() + "', " +
		 	 "lname ='" + custObj.getLname() + "', " +
		 	 "address ='" + custObj.getAddress() + "', " +
		 	 "zipcode =" + custObj.getZipcode() + ", " +
		 	 "phone =" + custObj.getPhone() + " " +
		     " WHERE id = '" + custObj.getId() + "'";
			
            System.out.println("Update query:" + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(int id)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Customer WHERE id = " +
				id + "";
                System.out.println(query);
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	    }
		return(rc);
	}
	
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> customlist = new ArrayList<Customer>();	
		
		String query =  buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
			while( results.next() )
			{
				Customer custObj = new Customer();
				custObj = buildCustomer(results);	
				customlist.add(custObj);	
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return customlist;
	}
	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer custObj = new Customer();
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			custObj = buildCustomer(results);
	 			stmt.close();
			}
		
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return custObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

	private Customer buildCustomer(ResultSet results)
      {   
		Customer custObj = new Customer();
          try
          {
                custObj.setId(results.getInt(1));
                custObj.setFname(results.getString(2));
                custObj.setLname(results.getString(3));
                custObj.setAddress(results.getString(4));
                custObj.setZipcode(results.getInt(5));
                custObj.setCity(searchCity(custObj.getZipcode()));
                custObj.setPhone(results.getString(6));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Customer object");
         }
         return custObj;
      }

	@Override
	public Customer searchCustomerFname(String fname, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Customer searchCustomerLname(String lname, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Customer searchCustomerPhone(int phone, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	
}  
    

